package edu.cicese.alz;

/*
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.JSONValue;
*/

import org.codehaus.jackson.JsonNode;
import org.codehaus.jackson.map.ObjectMapper;

import java.io.File;
import java.io.FileFilter;
import java.io.FileWriter;
import java.io.FilenameFilter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedList;

/**
 * Created by: Eduardo Quintana Contreras
 * Date: 22/11/11
 * Time: 12:23 PM
 */



public class DataCapture {
	// Database
	public Connection connection;
	public Statement statement;
	public ResultSet resultSet;

	// Data Types
	public final String ACCELEROMETER = "ACCELEROMETER";
	public final String AUDIO = "AUDIO";
	public final String BLUETOOTH = "BLUETOOTH";
	public final String GPS = "GPS";
	public final String CALLS = "CALLS";
	public final String STATES = "STATES";
	public final String WIFI = "WIFI";
	public final String BATTERY_LEVEL = "BATTERY_LEVEL";
	public final String BOOLEAN = "BOOLEAN";
	public final String NFC = "NFC";
	public final String GYROSCOPE = "GYROSCOPE";

	// JSON Nodes
	public final String DATA_TYPE = "dataType";
	public final String TIMESTAMP = "timestamp";
	// ACCELEROMETER
	public final String FRAME = "frame";
	// GPS
	public final String ACCURACY = "accuracy";
	public final String ALTITUDE = "altitude";
	public final String LATITUDE = "latitude";
	public final String LONGITUDE = "longitude";
	public final String PROVIDER = "provider";
	public final String SATELLITES = "satellites";
	public final String SPEED = "speed";
	// WIFI
	public final String BSSID = "bssid";
	public final String CAPABILITIES = "capabilities";
	public final String FREQUENCY = "frequency";
	public final String LEVEL = "level";
	public final String SSID = "ssid";
	// NFC
	public final String MESSAGE = "message";

	private int tableID;
	private long cAccelerometer;

	//TODO: REMOVE THIS, REDO ALL INFILES WITH A COMMA SEPARATOR
	private String sep = " ";

	private LinkedList<TableLimit> tableLimits = new LinkedList<>();

	public boolean openConnection() {
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			String url = "jdbc:mysql://localhost/alzheimerdb?useUnicode=true&characterEncoding=UTF-8";
			connection = DriverManager.getConnection(url, "root", "alz48");
			// Statements allow to issue SQL queries to the database
			statement = connection.createStatement();
			return true;
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public void createInfileData(String path, int userID) {
		if (userID == 12) {
			sep = ",";
		}
		tableID = 0;
		cAccelerometer = 500000l;
		try {
			if (openConnection()) {
				// Check user's accelerometer tables
				resultSet = statement.executeQuery(selectUserTablesQuery(userID));

				if (resultSet.next()) {
					tableID = resultSet.getInt(1);
				}

				if (tableID > 0) {
					resultSet = statement.executeQuery(selectTableLimitQuery(userID, tableID));
					if (resultSet.next()) {
						cAccelerometer = resultSet.getInt(5);
						tableLimits.add(new TableLimit(userID, tableID, resultSet.getDouble(3), resultSet.getDouble(4),
								cAccelerometer));
					}
				}

				File dir = new File(path);
				FileFilter filter = new FileFilter() {
					public boolean accept(File file) {
						return file.isDirectory();
					}
				};
				File[] files = dir.listFiles(filter);
				for (File f : files) {
					initiateCapture(f.getPath(), userID);
				}

				// Update `LAST` (last timestamp) and `COUNT` (measure qty) from table `table_limits`
				for (TableLimit tableLimit : tableLimits) {
//					System.out.println(userID + " " + tableID + " " + tableLimit.getLastTimestamp() + " " +
//							tableLimit.getCount());
					statement.executeUpdate(updateTableLimitQuery(tableLimit.getUserID(), tableLimit.getTableID(),
							tableLimit.getLastTimestamp(), tableLimit.getCount()));
				}

				close(connection, statement, resultSet);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}

	public void initiateCapture(String path, int userID) {
		ObjectMapper mapper = new ObjectMapper();

		// Filter files
		File dir = new File(path);
		FilenameFilter filter = new FilenameFilter() {
			public boolean accept(File dir, String name) {
				return name.toLowerCase().endsWith(".json");
			}
		};
		File[] files = dir.listFiles(filter);

		JsonNode attribute;
		String dataType;
		// Accelerometer
		double axisX, axisY, axisZ, timestamp;
//				int cAccelerometerAux = 0;
//				double timestampAux = 0;
		// Wi-Fi
		String bssid, ssid, capabilities;
		int frequency, level;
		// GPS
		double altitude, latitude, longitude, accuracy, speed;
		String provider;
		int satellites;
		// NFC
		String message;
		int messageID;

		boolean correctUser12 = true;

		// For each json log file
		for (File f : files) {
			try {
				JsonNode rootNode = mapper.readValue(f, JsonNode.class);
				for (JsonNode node : rootNode) {
					dataType = node.get(DATA_TYPE).getTextValue();


					switch (dataType) {
						case ACCELEROMETER:
							attribute = node.get(FRAME);
							Iterator<JsonNode> ite1 = attribute.getElements();

							while (ite1.hasNext()) {
								JsonNode accelerometer = ite1.next();
								axisX = accelerometer.get(0).getDoubleValue();
								axisY = accelerometer.get(1).getDoubleValue();
								axisZ = accelerometer.get(2).getDoubleValue();
								timestamp = accelerometer.get(3).getDoubleValue();

								cAccelerometer++;

								if (cAccelerometer > 500000l) {
									cAccelerometer = 1l;
									tableID++;

									// Create table `accelerometer_userID_tableID`
									statement.executeUpdate(createAccelerometerTableQuery(userID, tableID));

									// Update `TABLES` (table qty) in table `participant`
									statement.executeUpdate(updateUserTablesQuery(userID, tableID));

									// Insert row in `table_limits`
									statement.executeUpdate(insertTableLimitQuery(userID, tableID, timestamp,
											timestamp, 1));

									tableLimits.add(new TableLimit(userID, tableID, timestamp, timestamp, 1));
								}
								else {
									tableLimits.getLast().setLastTimestamp(timestamp);
									tableLimits.getLast().setCount(cAccelerometer);
								}

								saveDataFile("D:\\_proyectoAlz\\data\\accelerometer_" + userID + "_" + tableID +
										".dat", axisX + " " + axisY + " " + axisZ + " " + timestamp + " " +
										userID + "\n");
							}

							break;

						case WIFI:
							bssid = node.get(BSSID).getTextValue();
							ssid = node.get(SSID).getTextValue();
							capabilities = node.get(CAPABILITIES).getTextValue();
							frequency = node.get(FREQUENCY).getIntValue();
							level = node.get(LEVEL).getIntValue();
							timestamp = node.get(TIMESTAMP).getLongValue();

							saveDataFile("D:\\_proyectoAlz\\data\\wifi.dat", bssid + sep + capabilities + sep +
									frequency + sep + level + sep + ssid + sep + timestamp + sep +
									userID + "\n");
							break;
						case GPS:
							accuracy = node.get(ACCURACY).getDoubleValue();
							altitude = node.get(ALTITUDE).getDoubleValue();
							longitude = node.get(LONGITUDE).getDoubleValue();
							latitude = node.get(LATITUDE).getDoubleValue();
							speed = node.get(SPEED).getDoubleValue();
							provider = node.get(PROVIDER).getTextValue();
							satellites = node.get(SATELLITES).getIntValue();
							timestamp = node.get(TIMESTAMP).getLongValue();

							if (userID == 6) {
								timestamp = timestamp < 1325462463000l ? timestamp - 86400000 : timestamp - 172800000;
							}
							else if (userID == 3 || userID == 11 || userID == 13 || userID == 15) {
								timestamp = timestamp - 86400000;
							}
							else if (userID == 12) {
								if (timestamp == 1327078323429l) {
									correctUser12 = false;
								}
								if (correctUser12) {
									timestamp = timestamp - 86400000;
								}
							}

							saveDataFile("D:\\_proyectoAlz\\data\\gps.dat", accuracy + " " +
									altitude + " " + latitude + " " + longitude + " " + provider +
									" " + satellites + " " + speed + " " + timestamp + " " + userID + "\n");
							break;

						case NFC:
							message = node.get(MESSAGE).getTextValue();
							messageID = NFCActivity.getMessageID(message);
							timestamp = node.get(TIMESTAMP).getLongValue();
							saveDataFile("D:\\_proyectoAlz\\data\\nfc.dat", message + "," + messageID + "," +
									timestamp + "," + userID + "\n");
							break;
					}
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public String addApostrophes(String value) {
		if (value != null) {
			return "'" + value + "'";
		}
		return null;
	}

	public String createAccelerometerTableQuery(int userID, int tableID) {
		return "CREATE TABLE IF NOT EXISTS `accelerometer_" + userID + "_" + tableID  + "` (\n" +
				"`ID` int(11) NOT NULL AUTO_INCREMENT,\n" +
				"`AXIS_X` double NOT NULL,\n" +
				"`AXIS_Y` double NOT NULL,\n" +
				"`AXIS_Z` double NOT NULL,\n" +
				"`TIMESTAMP` bigint(14) unsigned NOT NULL,\n" +
				"`USER_ID` smallint(3) unsigned NOT NULL,\n" +
				"PRIMARY KEY (`ID`),\n" +
				"KEY `TIMESTAMP` (`TIMESTAMP`),\n" +
				"KEY `USER_ID` (`USER_ID`)\n" +
				") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci AUTO_INCREMENT=1;";
	}

	public String insertTableLimitQuery(int userID, int tableID, double timestamp1, double timestamp2, int count) {
		return "INSERT INTO table_limit VALUES (" +
				userID + ", " +
				tableID + ", " +
				timestamp1 + ", " +
				timestamp2 + ", " +
				count + ")";
	}

	public String selectTableLimitQuery(int userID, int tableID) {
		return "SELECT * FROM table_limit \n" +
				"WHERE `USER_ID` = " + userID + " AND \n" +
				"`TABLE_ID` = " + tableID;
	}

	public String updateTableLimitQuery(int userID, int tableID, double timestamp, long count) {
		return "UPDATE table_limit SET `LAST` = " + timestamp + ", `COUNT` = " + count + "\n" +
				"WHERE `USER_ID` = " + userID + " AND\n" +
				"`TABLE_ID` = " + tableID;
	}

	public String loadAccelerometerInfileQuery(int userID, int tableID) {
		return "LOAD DATA INFILE 'D:/_proyectoAlz/data/infiles/incense" + userID + "/accelerometer_"
				+ userID + "_" + tableID + ".dat'\n" +
				"INTO TABLE accelerometer_" + userID + "_" + tableID + "\n" +
				"FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\\n'" + "\n" +
				"(AXIS_X, AXIS_Y, AXIS_Z, TIMESTAMP, USER_ID);";
	}

	public String selectUserTablesQuery(int userID) {
		return "SELECT `TABLES` FROM participant WHERE `USER_ID` = " + userID;
	}

	public String updateUserTablesQuery(int userID, int tables) {
		return "UPDATE participant SET `TABLES` = " + tables + " WHERE `USER_ID` = " + userID;
	}
	

	private void saveDataFile(String filename, String row) {
		try {
			PrintWriter pw = new PrintWriter(new FileWriter(filename, true));
			pw.print(row);
			pw.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	private void insertData(int userID) {
		String queryStr;
		try {
			resultSet = statement.executeQuery(selectUserTablesQuery(userID));
			if (resultSet.next()) {
				tableID = resultSet.getInt(1);
			}
			for (int i = 1; i <= tableID; i++) {
				if (new File("D:\\_proyectoAlz\\data\\infiles\\incense" + userID + "\\accelerometer_" + userID +
						"_" + i + ".dat").exists()) {
					resultSet = statement.executeQuery(loadAccelerometerInfileQuery(userID, i));
				}
			}
			if (new File("D:\\_proyectoAlz\\data\\infiles\\incense" + userID + "\\wifi.dat").exists()) {
				if (userID == 12) {
					queryStr = "LOAD DATA INFILE 'D:/_proyectoAlz/data/infiles/incense" + userID + "/wifi.dat' " +
							"INTO TABLE wifi FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n';";
				}
				else {
					queryStr = "LOAD DATA INFILE 'D:/_proyectoAlz/data/infiles/incense" + userID + "/wifi.dat' " +
							"INTO TABLE wifi FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\\n';";
				}
				resultSet = statement.executeQuery(queryStr);
			}
			if (new File("D:\\_proyectoAlz\\data\\infiles\\incense" + userID + "\\gps.dat").exists()) {
				queryStr = "LOAD DATA INFILE 'D:/_proyectoAlz/data/infiles/incense" + userID + "/gps.dat' " +
						"INTO TABLE gps FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\\n';";
				resultSet = statement.executeQuery(queryStr);
			}
			if (new File("D:\\_proyectoAlz\\data\\infiles\\incense" + userID + "\\nfc.dat").exists()) {
				queryStr = "LOAD DATA INFILE 'D:/_proyectoAlz/data/infiles/incense" + userID + "/nfc.dat' " +
						"INTO TABLE nfc FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n';";
				resultSet = statement.executeQuery(queryStr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
//			close(connection, statement, resultSet);
		}
	}


	private void createTSReport(String path, int userID) {
		ObjectMapper mapper = new ObjectMapper();
		long threshold = 0;
		String diffHF, diffMF;

		File parentDir = new File(path);
		FileFilter filterDir = new FileFilter() {
			public boolean accept(File file) {
				return file.isDirectory();
			}
		};
		File[] dirs = parentDir.listFiles(filterDir);

		for (File d : dirs) {
			// Filter files
			File dir = new File(d.getPath());
			FilenameFilter filterJson = new FilenameFilter() {
				public boolean accept(File dir, String name) {
					return name.toLowerCase().endsWith(".json");
				}
			};
//			FileFilter filterSize = new FileFilter() {
//				public boolean accept(File file) {
//					return file.getTotalSpace();
//				}
//			};
			File[] files = dir.listFiles(filterJson);

			String dataType;
			long timestamp;
			long fileTimestamp;
			long diff;

			// For each json log file
			for (File jsonFile : files) {
				if (jsonFile.length() < 100000) {
					//dataDataSink1323331200259.json
					try {
						JsonNode rootNode = mapper.readValue(jsonFile, JsonNode.class);
						for (JsonNode node : rootNode) {
							dataType = node.get(DATA_TYPE).getTextValue();
							switch (dataType) {
								/*case ACCELEROMETER:
									fileTimestamp = extractTimestamp(jsonFile.getName());
									timestamp = node.get(TIMESTAMP).getLongValue();
//									diff = Math.abs(timestamp - fileTimestamp);
									diff = fileTimestamp - timestamp;
//									if (diff > threshold) {
										diffHF = String.format("%.2f", (float)((double)diff / 3600000d));
										diffMF = String.format("%.2f", (float)((double)diff / 60000d));
										saveDataFile("D:\\_proyectoAlz\\data\\" + userID + "_acc_report.txt", fileTimestamp + " " + timestamp + " " + diff + " " + diffHF + " " + diffMF + "\n");
//									}
									break;*/
								case NFC:
									fileTimestamp = extractTimestamp(jsonFile.getName());
									timestamp = node.get(TIMESTAMP).getLongValue();
//									diff = Math.abs(timestamp - fileTimestamp);
									diff = fileTimestamp - timestamp;
//									if (diff > threshold) {
										diffHF = String.format("%.2f", (float)((double)diff / 3600000d));
										diffMF = String.format("%.2f", (float)((double)diff / 60000d));
										saveDataFile("D:\\_proyectoAlz\\data\\" + userID + "_nfc_report.txt", fileTimestamp + " " + timestamp + " " + diff + " " + diffHF + " " + diffMF + "\n");
//									}
									break;
								case GPS:
									fileTimestamp = extractTimestamp(jsonFile.getName());
									timestamp = node.get(TIMESTAMP).getLongValue();
//									diff = Math.abs(timestamp - fileTimestamp);
									diff = fileTimestamp - timestamp;
//									if (diff > threshold) {
										diffHF = String.format("%.2f", (float)((double)diff / 3600000d));
										diffMF = String.format("%.2f", (float)((double)diff / 60000d));
										saveDataFile("D:\\_proyectoAlz\\data\\" + userID + "_gps_report.txt", fileTimestamp + " " + timestamp + " " + diff + " " + diffHF + " " + diffMF + "\n");
//									}
									break;
								case WIFI:
									fileTimestamp = extractTimestamp(jsonFile.getName());
									timestamp = node.get(TIMESTAMP).getLongValue();
//									diff = Math.abs(timestamp - fileTimestamp);
									diff = fileTimestamp - timestamp;
//									if (diff > threshold) {
										diffHF = String.format("%.2f", (float)((double)diff / 3600000d));
										diffMF = String.format("%.2f", (float)((double)diff / 60000d));
										saveDataFile("D:\\_proyectoAlz\\data\\" + userID + "_wifi_report.txt", fileTimestamp + " " + timestamp + " " + diff + " " + diffHF + " " + diffMF + "\n");
//									}
									break;
							}
						}

					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}
	}

	private void createTSReport2(String path, int userID) {
		ObjectMapper mapper = new ObjectMapper();

		File parentDir = new File(path);
		FileFilter filterDir = new FileFilter() {
			public boolean accept(File file) {
				return file.isDirectory();
			}
		};
		File[] dirs = parentDir.listFiles(filterDir);

		for (File d : dirs) {
			// Filter files
			File dir = new File(d.getPath());
			FilenameFilter filterJson = new FilenameFilter() {
				public boolean accept(File dir, String name) {
					return name.toLowerCase().endsWith(".json");
				}
			};
			File[] files = dir.listFiles(filterJson);

			String dataType;
			long lmTimestamp;
			long fileTimestamp;
			long diff;

			// For each json log file
			for (File jsonFile : files) {
				if (jsonFile.length() < 100000) {
					lmTimestamp = jsonFile.lastModified();
					fileTimestamp = extractTimestamp(jsonFile.getName());
//					diff = Math.abs(lmTimestamp - fileTimestamp);
					diff = lmTimestamp - fileTimestamp;
//					if (diff > 120000) {
						String diffF = String.format("%.2f", (float)((double)diff / 3600000d));
						String diffMF = String.format("%.2f", (float)((double)diff / 60000d));
						saveDataFile("D:\\_proyectoAlz\\data\\" + userID + "_lm_report.txt", fileTimestamp + " " + lmTimestamp + " " + diff + " " + diffF + " " + diffMF + "\n");
//					}
				}
			}
		}
	}

	private long extractTimestamp(String jsonFile) {
		return Long.parseLong(jsonFile.substring(12, 25));
	}

	// Release resources
	public void close(Connection con, Statement stmt, ResultSet rs) {
        if (rs != null) {
                try {
                        rs.close();
                } catch (SQLException e) {
                }
        }
        if (stmt != null) {
                try {
                        stmt.close();
                } catch (SQLException e) {
                }
        }
        if (con != null) {
                try {
                        con.close();
                } catch (SQLException e) {
                }
        }
	}



	public static void main(String[] args) {
//		new DataCapture().createInfileData("D:\\_proyectoAlz\\data\\users\\incense3", 3);
//		new DataCapture().createInfileData("D:\\_proyectoAlz\\data\\users\\cuidador16", 16);

		DataCapture dataCapture = new DataCapture();
		dataCapture.openConnection();
		dataCapture.insertData(16);




//		DataCapture dataCapture = new DataCapture();
//		dataCapture.openConnection();
//		for (int user = 6; user <= 10; user++) {
//			dataCapture.insertData(user);
//		}

//		dataCapture.insertData(3);

/*		for (int user = 1; user < 16; user++) {
			new DataCapture().createTSReport("D:\\_proyectoAlz\\data\\users\\incense" + user, user);
		}
		for (int user = 1; user < 16; user++) {
			new DataCapture().createTSReport2("D:\\_proyectoAlz\\data\\users\\incense" + user, user);
		}*/
	}
}
